﻿using System;
using System.Collections.Generic;

namespace ebay.Utility
{
    public class PubConst
    {
        public const String QueryMailCountString = @"SELECT 'msg-4352'=SUM(CASE WHEN (FolderID&0xFF00)=0x1100 THEN 1 ELSE 0 END)
    ,'msg-4608'=SUM(CASE WHEN (FolderID&0xFF00)=0x1200 THEN 1 ELSE 0 END)
    ,'msg-4624'=SUM(CASE WHEN (FolderID&0xFFF0)=0x1210 THEN 1 ELSE 0 END)
    ,'msg-4640'=SUM(CASE WHEN (FolderID&0xFFF0)=0x1220 THEN 1 ELSE 0 END)
    ,'msg-4641'=SUM(CASE WHEN FolderID=0x1221 THEN 1 ELSE 0 END)
    ,'msg-4642'=SUM(CASE WHEN FolderID=0x1222 THEN 1 ELSE 0 END)
    ,'msg-4643'=SUM(CASE WHEN FolderID=0x1223 THEN 1 ELSE 0 END)
    ,'msg-4644'=SUM(CASE WHEN FolderID=0x1224 THEN 1 ELSE 0 END)
    ,'msg-4645'=SUM(CASE WHEN FolderID=0x1225 THEN 1 ELSE 0 END)
    ,'msg-4646'=SUM(CASE WHEN FolderID=0x1226 THEN 1 ELSE 0 END)
    ,'msg-4656'=SUM(CASE WHEN (FolderID&0xFFF0)=0x1230 THEN 1 ELSE 0 END)
    ,'msg-4657'=SUM(CASE WHEN FolderID=0x1231 THEN 1 ELSE 0 END)
    ,'msg-4658'=SUM(CASE WHEN FolderID=0x1232 THEN 1 ELSE 0 END)
    ,'msg-4659'=SUM(CASE WHEN FolderID=0x1233 THEN 1 ELSE 0 END)
  FROM eBayMessage
  WHERE [Read]=0 AND eBayUserID=@eBayUserID";

        public const String QueryUserCaseInfoString = @"SELECT SUM(CASE WHEN ((CaseType='CANCEL_TRANSACTION' AND (CaseStatusValue='MY_RESPONSE_DUE' OR CaseStatusValue='OTHER' OR CaseStatusValue='OTHER_PARTY_RESPONSE_DUE')) OR (CaseType='UPI' AND (CaseStatusValue='MY_PAYMENT_DUE' OR CaseStatusValue='MY_RESPONSE_DUE' OR CaseStatusValue='OTHER' OR CaseStatusValue='OTHER_PARTY_RESPONSE_DUE'))) AND DATEADD(DAY,2,CreationDate)>GETDATE() THEN 1 ELSE 0 END) AS NotPayNew
    ,SUM(CASE WHEN ((CaseType='CANCEL_TRANSACTION' AND (CaseStatusValue='MY_RESPONSE_DUE' OR CaseStatusValue='OTHER' OR CaseStatusValue='OTHER_PARTY_RESPONSE_DUE')) OR (CaseType='UPI' AND (CaseStatusValue='MY_PAYMENT_DUE' OR CaseStatusValue='MY_RESPONSE_DUE' OR CaseStatusValue='OTHER' OR CaseStatusValue='OTHER_PARTY_RESPONSE_DUE'))) AND DATEADD(DAY,2,CreationDate)<GETDATE() THEN 1 ELSE 0 END) AS NotPayOld
    ,SUM(CASE WHEN ((CaseType='CANCEL_TRANSACTION' AND (CaseStatusValue='CANCELLED' OR CaseStatusValue='CLOSED' OR CaseStatusValue='CLOSED_FVFCREDIT' OR CaseStatusValue='CLOSED_NOFVFCREDIT' OR CaseStatusValue='CS_CLOSED' OR CaseStatusValue='EXPIRED')) OR (CaseType='UPI' AND (CaseStatusValue='CLOSED' OR CaseStatusValue='CLOSED_FVFCREDIT_NOSTRIKE' OR CaseStatusValue='CLOSED_FVFCREDIT_STRIKE' OR CaseStatusValue='CLOSED_NOFVFCREDIT_NOSTRIKE' OR CaseStatusValue='CLOSED_NOFVFCREDIT_STRIKE' OR CaseStatusValue='CS_CLOSED' OR CaseStatusValue='EXPIRED'))) THEN 1 ELSE 0 END) AS NotPayClosed
    ,SUM(CASE WHEN ((CaseType='EBP_SNAD' AND (CaseStatusValue='MY_RESPONSE_DUE' OR CaseStatusValue='MY_PAYMENT_DUE')) OR (CaseType='PAYPAL_SNAD' AND CaseStatusValue='MY_RESPONSE_DUE')) AND [Read]=0 THEN 1 ELSE 0 END) AS IADNew
    ,SUM(CASE WHEN ((CaseType='EBP_SNAD' AND (CaseStatusValue='MY_RESPONSE_DUE' OR CaseStatusValue='MY_PAYMENT_DUE')) OR (CaseType='PAYPAL_SNAD' AND CaseStatusValue='MY_RESPONSE_DUE')) AND [Read]=1 THEN 1 ELSE 0 END) AS IADProcessing
    ,SUM(CASE WHEN ((CaseType='EBP_SNAD' AND CaseStatusValue IN ('OTHER','OTHER_PARTY_CONTACTED_CS_AWAITING_RESPONSE','OTHER_PARTY_RESPONSE_DUE','PAID','WAITING_DELIVERY','YOU_CONTACTED_CS_ABOUT_CLOSED_CASE','YOU_CONTACTED_CS_AWAITING_RESPONSE')) OR (CaseType='PAYPAL_SNAD' AND CaseStatusValue IN ('OPEN','OTHER','OTHER_PARTY_RESPONSE_DUE'))) THEN 1 ELSE 0 END) AS IADProcessed
    ,SUM(CASE WHEN ((CaseType='EBP_SNAD' AND CaseStatusValue IN ('CASE_CLOSED_CS_RESPONDED','CLOSED','CS_CLOSED','EXPIRED')) OR (CaseType='PAYPAL_SNAD' AND CaseStatusValue='CLOSED')) THEN 1 ELSE 0 END) AS IADClosed
    ,SUM(CASE WHEN ((CaseType='EBP_INR' AND CaseStatusValue IN ('MY_PAYMENT_DUE','MY_RESPONSE_DUE')) OR (CaseType='INR' AND CaseStatusValue IN ('MY_PAYMENT_DUE','MY_RESPONSE_DUE')) OR (CaseType='PAYPAL_INR' AND CaseStatusValue='MY_RESPONSE_DUE')) AND [Read]=0 THEN 1 ELSE 0 END) AS DTNew
    ,SUM(CASE WHEN ((CaseType='EBP_INR' AND CaseStatusValue IN ('MY_PAYMENT_DUE','MY_RESPONSE_DUE')) OR (CaseType='INR' AND CaseStatusValue IN ('MY_PAYMENT_DUE','MY_RESPONSE_DUE')) OR (CaseType='PAYPAL_INR' AND CaseStatusValue='MY_RESPONSE_DUE')) AND [Read]=1 THEN 1 ELSE 0 END) AS DTProcessing
    ,SUM(CASE WHEN ((CaseType='EBP_INR' AND CaseStatusValue IN ('OTHER','OTHER_PARTY_CONTACTED_CS_AWAITING_RESPONSE','OTHER_PARTY_RESPONSE_DUE','PAID','YOU_CONTACTED_CS_ABOUT_CLOSED_CASE','YOU_CONTACTED_CS_AWAITING_RESPONSE')) OR (CaseType='INR' AND CaseStatusValue IN ('OTHER','OTHER_PARTY_CONTACTED_CS_AWAITING_RESPONSE','OTHER_PARTY_RESPONSE_DUE','PAID','YOU_CONTACTED_CS_ABOUT_CLOSED_CASE','YOU_CONTACTED_CS_AWAITING_RESPONSE')) OR (CaseType='PAYPAL_INR' AND CaseStatusValue IN ('OPEN','OTHER','OTHER_PARTY_RESPONSE_DUE'))) THEN 1 ELSE 0 END) AS DTProcessed
    ,SUM(CASE WHEN ((CaseType='EBP_INR' AND CaseStatusValue IN ('CASE_CLOSED_CS_RESPONDED','CLOSED','CS_CLOSED','EXPIRED')) OR (CaseType='INR' AND CaseStatusValue IN ('CLOSED','CS_CLOSED','EXPIRED')) OR (CaseType='PAYPAL_INR' AND CaseStatusValue='CLOSED')) THEN 1 ELSE 0 END) AS DTClosed
  FROM eBayUserCase o
  WHERE eBayUserID=@eBayUserID";

        public const String QueryFeedbackInfoString = @"SELECT SUM(CASE WHEN f.CommentType='Negative' AND f.HasResponse=0 THEN 1 ELSE 0 END) AS NotResponseNegative
    ,SUM(CASE WHEN f.CommentType='Negative' AND t.[Level]='常规跟进' AND f.HasReject=0 AND DATEADD(DAY,7,LastReplyDate)<GETDATE() THEN 1 ELSE 0 END) AS NormalNegative
    ,SUM(CASE WHEN f.CommentType='Negative' AND t.[Level]='高级跟进' AND f.HasReject=0 AND (LastReplyDate IS NULL OR LastReplyDate<CONVERT(VARCHAR(10),GETDATE(),120)) THEN 1 ELSE 0 END) AS AdvanceNegative
    ,SUM(CASE WHEN f.CommentType='Neutral' AND t.[Level]='高级跟进' AND f.HasReject=0 AND (LastReplyDate IS NULL OR LastReplyDate<CONVERT(VARCHAR(10),GETDATE(),120)) THEN 1 ELSE 0 END) AS AdvanceNeutral
    ,SUM(CASE WHEN f.CommentType='Negative' AND f.HasResponse=1 AND (t.[Level]<>'常规跟进' or f.HasReject=1 or DATEADD(DAY,7,LastReplyDate)>GETDATE()) AND (t.[Level]<>'高级跟进' OR f.HasReject=1 OR LastReplyDate>CONVERT(VARCHAR(10),GETDATE(),120)) THEN 1 ELSE 0 END) AS Negative
    ,SUM(CASE WHEN f.CommentType='Neutral' AND (t.[Level]<>'高级跟进' OR f.HasReject=1 OR LastReplyDate>=CONVERT(VARCHAR(10),GETDATE(),120)) THEN 1 ELSE 0 END) AS Neutral
    ,SUM(CASE WHEN f.CommentType='Positive' THEN 1 ELSE 0 END) AS Positive
    ,SUM(CASE WHEN f.CommentType='Positive' THEN 1 ELSE 0 END) AS Revised
  FROM eBayFeedback f LEFT JOIN eBayFeedbackFollowType t ON f.FollowLevel=t.Type
  WHERE eBayUserID=@eBayUserID";

        public static Dictionary<String, int> FlagNameValuePair = new Dictionary<String, int>() { { "gray", 0 }, { "blue", 1 }, { "green", 2 }, { "orange", 3 }, { "purple", 4 }, { "red", 5 }, { "yellow", 6 } };

        // 0117: Insert blacklist by order id on MessageDetail page
        public const String MailHandlerInsertBlacklistByOrderID = "0117";
        // 0118: Insert blacklist by message id on MessageDetail page
        public const String MailHandlerInsertBlacklistByMsg = "0118";
    }
}
